<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- saved from url=(0028)http://jdbi.org/archive.html -->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></meta>


<title>abacus-common</title>

<script type="text/javascript" src="./css/ga.js"></script>
<script type="text/javascript">
	if (top.location != self.location)
		top.location.replace(self.location);
</script>

<link href="./css/css" rel="stylesheet" type="text/css"></link>
<link href="./css/css(1)" rel="stylesheet" type="text/css"></link>
<link rel="stylesheet" href="./css/syntax.css" type="text/css"></link>
<link rel="stylesheet" href="./css/screen.css" type="text/css"></link>

<script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

  ga('create', 'UA-65247258-1', 'auto');
  ga('send', 'pageview');
</script>

</head>
<body>
	<div id="site">
		<h1>
			<a href="./index.html">abacus-common</a> <span id="navigation"> <a href="./docs.html">Docs</a> | <a href="./download.html">Download</a>
				| <a href="./api-docs/index.html">Javadoc</a> | <a href="./faq.html">FAQ</a> | <a href="./community.html">Community</a>
			</span>
		</h1>
	</div>

	<div id="content">

		<div id="page">
		
			<h1>SQL Builder</h1>
			<p><a href="./SQLBuilder_view.html">SQLBuilder</a> is one of the fantastic parts in abacus-common. 
			Writing and maintaining mass of SQL statements in String used to be a big trouble in the development of a lot of backend applications.
			   SQLBuild turns it into a pleasure to write much more readable/maintainable SQL with the concise APIs. Let's experience it by a few of examples:</p>
			   
<!-- HTML generated using hilite.me --><div style="background: #f8f8f8; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%">String sql <span style="color: #666666">=</span> E<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;id&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;firstName&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;lastName&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;id&quot;</span><span style="color: #666666">,</span> <span style="color: #666666">1)).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
<span style="color: #408080; font-style: italic">// sql: SELECT id AS &quot;id&quot;, first_name AS &quot;firstName&quot;, last_name AS &quot;lastName&quot; FROM account WHERE id = 1</span>
sqlExecutor<span style="color: #666666">.</span><span style="color: #7D9029">query</span><span style="color: #666666">(</span>sql<span style="color: #666666">).</span><span style="color: #7D9029">println</span><span style="color: #666666">();</span>

<span style="color: #408080; font-style: italic">// Generate the parameterized sql with &#39;?&#39; and improve the performance/security by PreparedStatement.</span>
Pair pair <span style="color: #666666">=</span> RE<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;id&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;firstName&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;lastName&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;id&quot;</span><span style="color: #666666">,</span> <span style="color: #666666">1)).</span><span style="color: #7D9029">pair</span><span style="color: #666666">();</span>
<span style="color: #408080; font-style: italic">// sql: SELECT id AS &quot;id&quot;, first_name AS &quot;firstName&quot;, last_name AS &quot;lastName&quot; FROM account WHERE id = ?</span>
sqlExecutor<span style="color: #666666">.</span><span style="color: #7D9029">query</span><span style="color: #666666">(</span>pair<span style="color: #666666">.</span><span style="color: #7D9029">sql</span><span style="color: #666666">,</span> pair<span style="color: #666666">.</span><span style="color: #7D9029">parameters</span><span style="color: #666666">).</span><span style="color: #7D9029">println</span><span style="color: #666666">();</span>

<span style="color: #408080; font-style: italic">// Generate the parameterized sql with named parameters and improve the performance/security by PreparedStatement,</span>
<span style="color: #408080; font-style: italic">// and supports entity/map as parameters.</span>
pair <span style="color: #666666">=</span> NE<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;id&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;firstName&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;lastName&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;id&quot;</span><span style="color: #666666">,</span> <span style="color: #666666">1)).</span><span style="color: #7D9029">pair</span><span style="color: #666666">();</span>
<span style="color: #408080; font-style: italic">// sql: SELECT id AS &quot;id&quot;, first_name AS &quot;firstName&quot;, last_name AS &quot;lastName&quot; FROM account WHERE id = :id</span>
sqlExecutor<span style="color: #666666">.</span><span style="color: #7D9029">query</span><span style="color: #666666">(</span>pair<span style="color: #666666">.</span><span style="color: #7D9029">sql</span><span style="color: #666666">,</span> pair<span style="color: #666666">.</span><span style="color: #7D9029">parameters</span><span style="color: #666666">).</span><span style="color: #7D9029">println</span><span style="color: #666666">();</span>
</pre></div>


			<p>(If you're confused by short name: <a href="./api-docs/com/landawn/abacus/util/SQLBuilder.E.html">E</a>, <a href="./api-docs/com/landawn/abacus/util/SQLBuilder.RE.html">RE</a>, <a href="./api-docs/com/landawn/abacus/util/SQLBuilder.NE.html">NE</a>, <a href="./api-docs/com/landawn/abacus/condition/ConditionFactory.L.html">L</a>, <a href="./api-docs/com/landawn/abacus/util/N.html">N</a>. Refer to <a href="./api-docs/com/landawn/abacus/util/SQLBuilder.html">SQLBuilder</a> for (E, RE, NE). 
			L is the short name for the factory of structured condition. 
			N is a general utility class for String/Array..., introduced at: <a href="./n.html">N</a>)</p>
			
			<p></p>
			<p>We can do even better by removing the magic Strings with the constants in the <a href="./code-generation.html">auto-generated classes for property name</a>. It will improve the maintainability.</p>
<!-- HTML generated using hilite.me --><div style="background: #f8f8f8; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%">String sql <span style="color: #666666">=</span> E<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> FIRST_NAME<span style="color: #666666">,</span> LAST_NAME<span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span>Account<span style="color: #666666">.</span><span style="color: #7D9029">_</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> <span style="color: #666666">1)).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
sqlExecutor<span style="color: #666666">.</span><span style="color: #7D9029">query</span><span style="color: #666666">(</span>sql<span style="color: #666666">).</span><span style="color: #7D9029">println</span><span style="color: #666666">();</span>

<span style="color: #408080; font-style: italic">// Actually we don&#39;t need and should not set the concrete parameters for generation of parameterized sql,</span>
<span style="color: #408080; font-style: italic">// Just set parameter value with question mark expression.</span>
sql <span style="color: #666666">=</span> RE<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> FIRST_NAME<span style="color: #666666">,</span> LAST_NAME<span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span>Account<span style="color: #666666">.</span><span style="color: #7D9029">_</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> L<span style="color: #666666">.</span><span style="color: #7D9029">QME</span><span style="color: #666666">)).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
sqlExecutor<span style="color: #666666">.</span><span style="color: #7D9029">query</span><span style="color: #666666">(</span>sql<span style="color: #666666">,</span> <span style="color: #666666">1).</span><span style="color: #7D9029">println</span><span style="color: #666666">();</span>

<span style="color: #408080; font-style: italic">// Parameters with format: Array/List/Map/Entity are supported by named SQL</span>
sql <span style="color: #666666">=</span> NE<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> FIRST_NAME<span style="color: #666666">,</span> LAST_NAME<span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span>Account<span style="color: #666666">.</span><span style="color: #7D9029">_</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span>ID<span style="color: #666666">,</span> L<span style="color: #666666">.</span><span style="color: #7D9029">QME</span><span style="color: #666666">)).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
sqlExecutor<span style="color: #666666">.</span><span style="color: #7D9029">query</span><span style="color: #666666">(</span>sql<span style="color: #666666">,</span> <span style="color: #666666">1).</span><span style="color: #7D9029">println</span><span style="color: #666666">();</span>
</pre></div>


			<p></p>
			<p>Or use the class directly:</p>
<!-- HTML generated using hilite.me --><div style="background: #f8f8f8; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%">String sql <span style="color: #666666">=</span> NE<span style="color: #666666">.</span><span style="color: #7D9029">selectFrom</span><span style="color: #666666">(</span>Account<span style="color: #666666">.</span><span style="color: #7D9029">class</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>eq<span style="color: #666666">(</span>ID<span style="color: #666666">)).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
<span style="color: #408080; font-style: italic">// sql: SELECT id AS &quot;id&quot;, gui AS &quot;gui&quot;, first_name AS &quot;firstName&quot;, last_name AS &quot;lastName&quot;, status AS &quot;status&quot;, last_update_time AS &quot;lastUpdateTime&quot;, create_time AS &quot;createTime&quot;, devices AS &quot;devices&quot; FROM account WHERE id = :id</span>
</pre></div>


			<p></p>
			<p>Here is the performance test running on a laptop with: CPU: Intel i7-3520M 2.9GHz, Meory: 8G, OS: Windows 7 64-bit, JDK: 1.7.0_80:</p>
<!-- HTML generated using hilite.me --><div style="background: #f8f8f8; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%">Profiler<span style="color: #666666">.</span><span style="color: #7D9029">run</span><span style="color: #666666">(</span><span style="color: #008000; font-weight: bold">new</span> Runnable<span style="color: #666666">()</span> <span style="color: #666666">{</span>
    <span style="color: #AA22FF">@Override</span>
    <span style="color: #008000; font-weight: bold">public</span> <span style="color: #B00040">void</span> <span style="color: #0000FF">run</span><span style="color: #666666">()</span> <span style="color: #666666">{</span>
        String sql <span style="color: #666666">=</span> E<span style="color: #666666">.</span><span style="color: #7D9029">insert</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;gui&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;firstName&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;lastName&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;lastUpdateTime&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;createTime&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">into</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
        <span style="color: #408080; font-style: italic">// sql: INSERT INTO account (gui, first_name, last_name, last_update_time, create_time) VALUES (?, ?, ?, ?, ?)</span>
        assertEquals<span style="color: #666666">(102,</span> sql<span style="color: #666666">.</span><span style="color: #7D9029">length</span><span style="color: #666666">());</span>

        sql <span style="color: #666666">=</span> NE<span style="color: #666666">.</span><span style="color: #7D9029">select</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;gui&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;firstName&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;lastName&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;lastUpdateTime&quot;</span><span style="color: #666666">,</span> <span style="color: #BA2121">&quot;createTime&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">from</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;account&quot;</span><span style="color: #666666">).</span><span style="color: #7D9029">where</span><span style="color: #666666">(</span>L<span style="color: #666666">.</span><span style="color: #7D9029">eq</span><span style="color: #666666">(</span><span style="color: #BA2121">&quot;id&quot;</span><span style="color: #666666">,</span> <span style="color: #666666">1)).</span><span style="color: #7D9029">sql</span><span style="color: #666666">();</span>
        <span style="color: #408080; font-style: italic">// sql: SELECT gui AS &quot;gui&quot;, first_name AS &quot;firstName&quot;, last_name AS &quot;lastName&quot;, last_update_time AS &quot;lastUpdateTime&quot;, create_time AS &quot;createTime&quot; FROM account WHERE id = :id</span>
        assertEquals<span style="color: #666666">(166,</span> sql<span style="color: #666666">.</span><span style="color: #7D9029">length</span><span style="color: #666666">());</span>
    <span style="color: #666666">}</span>
<span style="color: #666666">},</span> <span style="color: #666666">16,</span> <span style="color: #666666">200000,</span> <span style="color: #666666">3).</span><span style="color: #7D9029">printResult</span><span style="color: #666666">();</span>
</pre></div>


	<p></p>
			<p>Test Result:</p>	
========================================================================================================================
<br/>(unit: milliseconds)
<br/>threadNum=16; loops=200000
<br/>totalElapsedTime: 5750
<br/>
<br/>maxMethodTime(run): 244
<br/>minMethodTime(run): 0
<br/>
<table width="600" border="1">
<tr>
<th>method name</th>
<th>avg time</th>
<th>min time</th>
<th>max time</th>
<th>0.01% &gt;=</th>
<th>0.1% &gt;=</th>
<th>1% &gt;=</th>
<th>10% &gt;=</th>
<th>20% &gt;=</th>
<th>50% &gt;=</th>
<th>80% &gt;=</th>
<th>90% &gt;=</th>
<th>99% &gt;=</th>
<th>99.9% &gt;=</th>
<th>99.99% &gt;=</th>
</tr>
<tr>
<td>run</td>
<td>0.0139</td>
<td>0</td>
<td>244</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
</tr>
</table>
========================================================================================================================

<p>It just took about 5750 milliseconds to generate 6.4 million sql scripts with 16 threads. 
The avarage time is 0.0139 milliseconds and maxmium time is 244 milliseconds. 
99.99% is less than 1 milliseconds.</p>
<p>Although it's super fast to generate the short sql, 
it's still one of the good practices to define the most used/static/long sql scripts in String constants or sql mapper xml file.</p>

		</div>

	</div>
</body>

</html>